<?php


namespace common\helpers;


use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExcelExport
{
    protected $data;
    protected $header;
    protected $fields;
    protected $fileName;
    protected $spreadsheet;
    protected $sheet;

    /**
     * ExcelExport constructor.
     * @param $data  "导出的数据列表"
     * @param $header "EXCEL表头"
     * @param $fields  "表头对应的数据库字段"
     * @param string $fileName “文件名”
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function __construct($data,array $header,array $fields, $fileName = "list.xlsx")
    {
        $this->data = $data;
        $this->header = $header;
        $this->fields = $fields;
        $this->fileName = $fileName;
        $this->spreadsheet = new Spreadsheet();
        $this->sheet = $this->spreadsheet->getActiveSheet();
    }

    /**
     * Description:设置表头
     * Author: Sam
     * DateTime: 2018/12/4 14:24
     */
    private function setHeader()
    {
        $len = count($this->header);
        for ($i = 0; $i < $len; $i++) {
            if($i<=25){
                $this->sheet->setCellValue(chr(65 + $i) . "1", $this->header[$i]);
            }else{
                $n = floor($i/26);
                $this->sheet->setCellValue(chr(65+$n-1).chr(65+($i%26))."1", $this->header[$i]);
            }

        }
    }

    /**
     * Description:导出操作
     * Author: Sam
     * DateTime: 2018/12/4 14:24
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function export()
    {
        ob_clean();
        $this->setHeader();
        foreach ($this->data as $index => $item) {
            $row = $index + 2;
            $len = count($this->fields);
            for ($i = 0; $i < $len; $i++){
                if($i<=25){
                    $this->sheet->setCellValueExplicit(chr(65+$i).$row,$item[$this->fields[$i]],DataType::TYPE_STRING);
                }else{
                    $n = floor($i/26);
                    $this->sheet->setCellValueExplicit(chr(65+$n-1).chr(65+($i%26)).$row,$item[$this->fields[$i]],DataType::TYPE_STRING);
                }
            }
        }
        $writer = new Xlsx($this->spreadsheet);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        header('Content-Disposition: attachment;filename="'.$this->fileName.'"');//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }

}